﻿#include "stdafx.h"
#include "TeacherDB.h"
#include <string>

TeacherDB::TeacherDB()
{
}
Teacher TeacherDB::selectById(const std::string &id)
{
	Teacher teacher;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from teacher where id=" + id);

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string id = res->getString(1);
			string name = res->getString(2);
			string dn = res->getString(3);
			teacher.setId(id);
			teacher.setName(name);
			teacher.setDeptName(dn);

		}
		delete res;
		delete stmt;
		delete con;
		return teacher;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
list<Teacher> TeacherDB::selectAll()
{
	list<Teacher> teachers;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from teacher");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string id = res->getString(1);
			string name = res->getString(2);
			string dn = res->getString(3);

			Teacher teacher;
			teacher.setId(id);
			teacher.setName(name);
			teacher.setDeptName(dn);

			teachers.push_back(teacher);
		}
		delete res;
		delete stmt;
		delete con;
		return teachers;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void TeacherDB::deleteById(const std::string &i)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("DELETE from teacher where id=" + i);

		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void TeacherDB::update(Teacher &t)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet * res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("update teacher set name='" + t.getName() + "',dept_name='" + t.getDeptName() + "' where id='" + t.getId() + "'");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void TeacherDB::insert(Teacher &t)
{
	try {
		Connection *con;
		Statement *stmt;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("insert into teacher values('" + t.getId() + "','" + t.getName() + "','" + t.getDeptName() + "')");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

TeacherDB::~TeacherDB()
{
}
